select * from[dbo].[CLAIM_BATCH_LOG] order by last_updated_date desc
===
select *
from F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS
where etl_batch_id ='DL_837P_20171016'
===
select claim_key, preauth_number, service_date_from
from F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS
where etl_batch_id ='CCN4_837P_20121107'
and preauth_number is not NULL
====
select	distinct
-- Claim ID --Was sent by Vivien
DIM_VA_CLAIM.claim_id 	
,DIM_VA_CLAIM.claim_key	
,'--VA Referral ID'	
,DIM_VA_CLAIM.preauth_number 'VA Authorization ID'
,DIM_VA_CLAIM.patient_account_number 'Patient Control/Account Number'
,DIM_VA_PROGRAM.program_name 
,DIM_VA_CLAIM.station_key -- VA Station --
-- Other Health Insurance --
,DIM_VA_CLAIM.other_insurance
,CASE WHEN DIM_VA_CLAIM.other_insurance = 'Y' THEN 
	(select CONCAT(insured_id, '*',insured_name, '*',paid_amount)
	 from claim_insurance where claim_key = DIM_PROFESSIONAL_CLAIM.claim_key)
ELSE 'No OHI' END AS 'Other Health Insurance'
-- Claim Payer --
,CLAIM_PAYER.claim_key
,CLAIM_PAYER.claim_payer_key
,CLAIM_PAYER.payer_name
,CLAIM_PAYER.plan_id
,CLAIM_PAYER.assignment_of_benefit
,CLAIM_PAYER.prior_payment
,CLAIM_PAYER.estimated_amount_due
-- Billing Provider --
,DIM_VA_CLAIM.billing_provider_TIN 'Billing Provider TIN' 
,billing_provider.tax_id 'Billing Provider Tax ID' 
,billing_provider.provider_name 'Billing Provider Name'
,billing_provider.npi 'Billing Provider NPI' 
-- Referring Provider --
,referring_provider.tax_id 'Referring Provider Tax ID' 
,referring_provider.provider_name 'Referring Provider Name'
,referring_provider.npi 'Referring Provider NPI' 
-- Service Facility --
,service_facility.npi 'Service Facility NPI'  	
,DIM_VA_CLAIM.service_facility_zip 'Service Facility Zip Code'	
,'--Service Facility Taxonomy'	
,DIM_DIAGNOSIS_CODE.diagnosis_code 'Claim Diagnosis Code'	
-- Patient/Veteran --
,DIM_PATIENT.ICN 'Veteran ICN'	
,DIM_PATIENT.ICN_status 'Veteran ICN Status'
,DIM_PATIENT.date_of_birth 'Veteran Date of Birth'	
,DIM_PATIENT.sex 'Veteran Gender'	
,DIM_PATIENT.postal_code 'Veteran Zip Code'	
,DIM_PATIENT.last_name 'Veteran Last Name'
,DIM_PATIENT.first_name 'Veteran First Name'
,DIM_PATIENT.middle_initial 'Veteran MI'
,DIM_PATIENT.address1 'Veteran Address - Street '
,DIM_PATIENT.city 'Veteran Address - City'
,DIM_PATIENT.postal_code 'Veteran Address - Zio'
-- Claim Line --
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.claim_key
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.claim_detail_key
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.procedure_key
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.pos_key
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.preauth_number
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.charge_amt
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.unit
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.unit_label
-- Rendering Provider --
,rendering_provider.npi 'Rendering Provider NPI' 
,rendering_provider.provider_name 'Rendering Provider Name'
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.rendering_provider_key
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.rendering_provider_qualifier
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.rendering_provider_taxonomy
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.service_date_from
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.service_date_to
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.service_line_number
,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.source_claim_line_id
,CLAIM_DIAGNOSIS.diagnosis_key
,DIM_DIAGNOSIS_CODE.diagnosis_key
,DIM_DIAGNOSIS_CODE.diagnosis_code
,DIM_DIAGNOSIS_CODE.short_description
,DIM_DIAGNOSIS_CODE.version
from DIM_VA_PROGRAM
	,DIM_VA_STATION
	,DIM_CLAIM_FORM_TYPE
	,DIM_PATIENT 
	,DIM_PROVIDER billing_provider
	,DIM_PROVIDER referring_provider
	,DIM_PROVIDER rendering_provider
	,DIM_PROVIDER service_facility
	,DIM_VA_CLAIM	
	,DIM_DIAGNOSIS_CODE
	,DIM_PROFESSIONAL_CLAIM 
	,CLAIM_DIAGNOSIS 
	,CLAIM_PAYER
	,F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS
	,DIM_PROCEDURE_CODE
	----,CLAIM_CONDITION_CODE -- no data loaded yet
where DIM_CLAIM_FORM_TYPE.form_code = '837P'
and claim_id = '101'
	and DIM_VA_CLAIM.form_type_key = DIM_CLAIM_FORM_TYPE.form_type_key	
	and DIM_VA_CLAIM.program_key = DIM_VA_PROGRAM.program_key
	and DIM_VA_CLAIM.station_key = DIM_VA_STATION.station_key
	and DIM_VA_CLAIM.claim_key = DIM_PROFESSIONAL_CLAIM.claim_key
	and DIM_VA_CLAIM.billing_provider_key = billing_provider.provider_key	
	and DIM_VA_CLAIM.referring_provider_key = referring_provider.provider_key	
	and DIM_VA_CLAIM.service_facility_key = service_facility.provider_key	
	and DIM_VA_CLAIM.claim_key = DIM_PROFESSIONAL_CLAIM.claim_key	
	and DIM_VA_CLAIM.patient_key = DIM_PATIENT.patient_key	
	and CLAIM_DIAGNOSIS.claim_key_pr = DIM_PROFESSIONAL_CLAIM.claim_key	
	and CLAIM_DIAGNOSIS.claim_diagnosis_key = DIM_DIAGNOSIS_CODE.DIAGNOSIS_key	
	and CLAIM_PAYER.claim_key = DIM_VA_CLAIM.claim_key
	and F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.claim_key = DIM_PROFESSIONAL_CLAIM.claim_key
	and rendering_provider.provider_key = F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.rendering_provider_key
	and DIM_PROCEDURE_CODE.procedure_key = F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS.procedure_key
order by 2

